from flask import Flask, render_template, request, redirect, flash
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import asc, desc

app = Flask(__name__)

app.config.from_pyfile("config.py")

db = SQLAlchemy(app)


class Author(db.Model):
    __tablename__ = "authors"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True)
    password = db.Column(db.String(80), nullable=False)
    age = db.Column(db.Integer, default=0)


    # 参数1：该字段指向那个类， 参数2  反向指定 给Blog也指定一个属性来指向Author，参数3 什么时候获取数据，dynamic 表示只有在all() first()获取数据
    blogs = db.relationship("Blog", backref="author", lazy="dynamic")


class Blog(db.Model):
    __tablename__ = "blogs"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(20), unique=True)
    content = db.Column(db.Text, nullable=False)
    # 外键指定
    author_id = db.Column(db.Integer, db.ForeignKey("authors.id"))


def create():
    db.create_all()


def add():
    author1 = Author(name="金庸", password="123", age=100)
    author2 = Author(name="古龙", password="123", age=60)

    blog1 = Blog(title="天龙八部", content="我的内容")

    blog2 = Blog(title="侠客行", content="我的内容")

    blog3 = Blog(title="陆小凤传奇", content="我的内容")

    db.session.add(author1)
    db.session.add(author2)
    db.session.add(blog1)
    db.session.add(blog2)
    db.session.add(blog3)

    # 这样子指定外键  通过关系的方式来完成外键
    author1.blogs.append(blog1)
    author1.blogs.append(blog2)
    author2.blogs.append(blog3)

    db.session.commit()


def query():
    # 根据一个博客 去找作者信息
    blog = Blog.query.filter(Blog.id == 3).first()
    author = blog.author
    print(blog.author)

    # 根据作者 去找所有博客
    blogs = author.blogs.all()
    print(blogs)


def test_order():
    # 排序操作
    users = Author.query.order_by(desc(Author.id)).all()
    print(users)


if __name__ == '__main__':
    query()
